<!doctype html>



  


<html class="theme-next mist use-motion">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>
<meta name="baidu-site-verification" content="zD47G9xe42" />


<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />












  
  
  <link href="/vendors/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />




  
  
  
  

  
    
    
  

  

  

  

  

  
    
    
    <link href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">
  






<link href="/vendors/font-awesome/css/font-awesome.min.css?v=4.4.0" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.0.1" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="MySQL巩固与加强," />





  <link rel="alternate" href="/atom.xml" title="uiste" type="application/atom+xml" />




  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.0.1" />






<meta name="description" content="多表查询分类需求：在设计数据表时，那么将现实中的一个实体设计为一个表，但在查询数据时，很多时候会将具有关系的不同实体表中的数据一同取出来。语法：    select * from 表A,表B 【where 子句】说明：    从多个表中获取where子句的匹配条件进行获取数据    如果省略where子句，那么得到的结果是一个笛卡尔积笛卡尔积：    得到的结果集的记录数是：两个表的记录数的乘积">
<meta property="og:type" content="article">
<meta property="og:title" content="Day10-MySQL 多表查询、视图、事物、索引">
<meta property="og:url" content="http://blog.uiste.com/20160902-1.html">
<meta property="og:site_name" content="uiste">
<meta property="og:description" content="多表查询分类需求：在设计数据表时，那么将现实中的一个实体设计为一个表，但在查询数据时，很多时候会将具有关系的不同实体表中的数据一同取出来。语法：    select * from 表A,表B 【where 子句】说明：    从多个表中获取where子句的匹配条件进行获取数据    如果省略where子句，那么得到的结果是一个笛卡尔积笛卡尔积：    得到的结果集的记录数是：两个表的记录数的乘积">
<meta property="og:updated_time" content="2016-09-02T06:12:12.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Day10-MySQL 多表查询、视图、事物、索引">
<meta name="twitter:description" content="多表查询分类需求：在设计数据表时，那么将现实中的一个实体设计为一个表，但在查询数据时，很多时候会将具有关系的不同实体表中的数据一同取出来。语法：    select * from 表A,表B 【where 子句】说明：    从多个表中获取where子句的匹配条件进行获取数据    如果省略where子句，那么得到的结果是一个笛卡尔积笛卡尔积：    得到的结果集的记录数是：两个表的记录数的乘积">



<script type="text/javascript" id="hexo.configuration">
  var NexT = window.NexT || {};
  var CONFIG = {
    scheme: 'Mist',
    sidebar: {"position":"left","display":"post"},
    fancybox: true,
    motion: true,
    duoshuo: {
      userId: 0,
      author: '博主'
    }
  };
</script>




  <link rel="canonical" href="http://blog.uiste.com/20160902-1.html"/>

  <title>
 Day10-MySQL 多表查询、视图、事物、索引 | uiste 
</title>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  



  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?b58b87af012832b8ae35e33bb45b44fa";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>








  
  
    
  

  <div class="container one-collumn sidebar-position-left page-post-detail ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-meta ">
  

  <div class="custom-logo-site-title">
    <a href="/"  class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <span class="site-title">uiste</span>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>
  <p class="site-subtitle">uiste个人博客</p>
</div>

<div class="site-nav-toggle">
  <button>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
  </button>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      

      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br />
            
            搜索
          </a>
        </li>
      

            <li class="menu-item menu-item-search"> <a title="把这个链接拖到你的Chrome收藏夹工具栏中" href='javascript:(function() {
    function c() {
        var e = document.createElement("link");
        e.setAttribute("type", "text/css");
        e.setAttribute("rel", "stylesheet");
        e.setAttribute("href", f);
        e.setAttribute("class", l);
        document.body.appendChild(e)
    }
 
    function h() {
        var e = document.getElementsByClassName(l);
        for (var t = 0; t < e.length; t++) {
            document.body.removeChild(e[t])
        }
    }
 
    function p() {
        var e = document.createElement("div");
        e.setAttribute("class", a);
        document.body.appendChild(e);
        setTimeout(function() {
            document.body.removeChild(e)
        }, 100)
    }
 
    function d(e) {
        return {
            height : e.offsetHeight,
            width : e.offsetWidth
        }
    }
 
    function v(i) {
        var s = d(i);
        return s.height > e && s.height < n && s.width > t && s.width < r
    }
 
    function m(e) {
        var t = e;
        var n = 0;
        while (!!t) {
            n += t.offsetTop;
            t = t.offsetParent
        }
        return n
    }
 
    function g() {
        var e = document.documentElement;
        if (!!window.innerWidth) {
            return window.innerHeight
        } else if (e && !isNaN(e.clientHeight)) {
            return e.clientHeight
        }
        return 0
    }
 
    function y() {
        if (window.pageYOffset) {
            return window.pageYOffset
        }
        return Math.max(document.documentElement.scrollTop, document.body.scrollTop)
    }
 
    function E(e) {
        var t = m(e);
        return t >= w && t <= b + w
    }
 
    function S() {
        var e = document.createElement("audio");
        e.setAttribute("class", l);
        e.src = i;
        e.loop = false;
        e.addEventListener("canplay", function() {
            setTimeout(function() {
                x(k)
            }, 500);
            setTimeout(function() {
                N();
                p();
                for (var e = 0; e < O.length; e++) {
                    T(O[e])
                }
            }, 15500)
        }, true);
        e.addEventListener("ended", function() {
            N();
            h()
        }, true);
        e.innerHTML = " <p>If you are reading this, it is because your browser does not support the audio element. We recommend that you get a new browser.</p> <p>";
        document.body.appendChild(e);
        e.play()
    }
 
    function x(e) {
        e.className += " " + s + " " + o
    }
 
    function T(e) {
        e.className += " " + s + " " + u[Math.floor(Math.random() * u.length)]
    }
 
    function N() {
        var e = document.getElementsByClassName(s);
        var t = new RegExp("\\b" + s + "\\b");
        for (var n = 0; n < e.length; ) {
            e[n].className = e[n].className.replace(t, "")
        }
    }
 
    var e = 30;
    var t = 30;
    var n = 350;
    var r = 350;
    var i = "//s3.amazonaws.com/moovweb-marketing/playground/harlem-shake.mp3";
    var s = "mw-harlem_shake_me";
    var o = "im_first";
    var u = ["im_drunk", "im_baked", "im_trippin", "im_blown"];
    var a = "mw-strobe_light";
    var f = "//s3.amazonaws.com/moovweb-marketing/playground/harlem-shake-style.css";
    var l = "mw_added_css";
    var b = g();
    var w = y();
    var C = document.getElementsByTagName("*");
    var k = null;
    for (var L = 0; L < C.length; L++) {
        var A = C[L];
        if (v(A)) {
            if (E(A)) {
                k = A;
                break
            }
        }
    }
    if (A === null) {
        console.warn("Could not find a node of the right size. Please try a different page.");
        return
    }
    c();
    S();
    var O = [];
    for (var L = 0; L < C.length; L++) {
        var A = C[L];
        if (v(A)) {
            O.push(A)
        }
    }
})()    '>High</a> </li>

    </ul>
  

 
    <div class="site-search">
      
  <div class="popup">
 <span class="search-icon fa fa-search"></span>
 <input type="text" id="local-search-input">
 <div id="local-search-result"></div>
 <span class="popup-btn-close">close</span>
</div>


    </div>
   
</nav>

 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                Day10-MySQL 多表查询、视图、事物、索引
              
            
          </h1>
        

        <div class="post-meta">
        
  <span id="busuanzi_container_page_pv">阅读量 <span id="busuanzi_value_page_pv"></span> 次</span>

          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-09-02T17:18:51+08:00" content="2016-09-02">
              2016-09-02
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/MySQL/" itemprop="url" rel="index">
                    <span itemprop="name">MySQL</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/20160902-1.html#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="20160902-1.html" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

          
        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        <h3 id="多表查询分类"><a href="#多表查询分类" class="headerlink" title="多表查询分类"></a>多表查询分类</h3><p>需求：在设计数据表时，那么将现实中的一个实体设计为一个表，但在查询数据时，很多时候会将具有关系的不同实体表中的数据一同取出来。<br>语法：<br>    <code>select * from 表A,表B 【where 子句】</code><br>说明：<br>    从多个表中获取where子句的匹配条件进行获取数据<br>    如果省略where子句，那么得到的结果是一个笛卡尔积<br>笛卡尔积：<br>    得到的结果集的记录数是：两个表的记录数的乘积<br>    字段数：两个表的字段数的和</p>
<h4 id="union联合查询"><a href="#union联合查询" class="headerlink" title="union联合查询"></a>union联合查询</h4><p>注意：</p>
<blockquote>
<p>对同一个表的不同的部分进行不同的操作<br>一般用于对比较大的表进行分表存储，联合查询</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div></pre></td><td class="code"><pre><div class="line">mysql&gt; (select * from stu where sex=1 order by age limit 999)union(select * from stu where sex=0 order by age desc limit 999);</div><div class="line">+----+--------+----------+------+------+------+</div><div class="line">| id | s_num  | s_name   | sex  | age  | c_id |</div><div class="line">+----+--------+----------+------+------+------+</div><div class="line">|  7 | it007  | songjiu  | 1    |   18 |    2 |</div><div class="line">|  1 | it001  | zhangsan | 1    |   20 |    1 |</div><div class="line">|  6 | it006  | wangwang | 1    |   20 |    1 |</div><div class="line">|  9 | it009  | zhener   | 1    |   23 |    1 |</div><div class="line">| 11 | it0011 | wu       | 1    |   23 |    3 |</div><div class="line">|  2 | it002  | lisi     | 1    |   26 |    2 |</div><div class="line">| 10 | it0010 | qianqian | 1    |   26 |    1 |</div><div class="line">|  4 | it004  | zhaoliu  | 1    |   30 |    3 |</div><div class="line">| 13 | it0013 | deng     | 0    |   34 |    5 |</div><div class="line">|  5 | it005  | tianqi   | 0    |   28 |    1 |</div><div class="line">| 12 | it0012 | chen     | 0    |   26 |    3 |</div><div class="line">|  3 | it003  | wangwu   | 0    |   22 |    2 |</div><div class="line">|  8 | it008  | zhousan  | 0    |   19 |    2 |</div><div class="line">+----+--------+----------+------+------+------+</div><div class="line">13 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="内连接【inner-join】"><a href="#内连接【inner-join】" class="headerlink" title="内连接【inner join】"></a>内连接【inner join】</h4><p>语法一：<code>select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段</code></p>
<blockquote>
<p>inner是可以省略的</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo inner join stuMarks on stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>语法二：<code>select 列名 from 表1,表2 where 表1.公共字段=表2.公共字段</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo,stuMarks where stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>select <em> from 表1 inner join 表2 on 表1.公共字段=表2.公共字段和select </em> from 表2 inner join 表1 on 表1.公共字段=表2.公共字段是否一样？<br>答：一样的，因为内连接显示的是两个表的公共记录。</p>
<p>三个表的内连接如何实现？<br>答：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段</p>
</blockquote>
<h4 id="左外连接【left-join】"><a href="#左外连接【left-join】" class="headerlink" title="左外连接【left join】"></a>左外连接【left join】</h4><p>以左边的表为标准，如果右边的表没有对应的记录，用NULL填充。<br>语法：<code>select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo left join stuMarks on stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       | NULL    | NULL   |        NULL |    NULL |</div><div class="line">| s25319 | 梅超风       | 女     |     23 |       5 | 河北       | NULL    | NULL   |        NULL |    NULL |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">7 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>select <em> from 表1 left join 表2 on 表1.公共字段=表2.公共字段和select </em> from 表2 left join 表1 on 表1.公共字段=表2.公共字段是否一样？<br>答：不一样，第一个SQL语句以表1为准，第二个SQL语句以表2为准</p>
</blockquote>
<h4 id="右外连接【right-join】"><a href="#右外连接【right-join】" class="headerlink" title="右外连接【right join】"></a>右外连接【right join】</h4><p>以右边的表为标准，如果右边的表没有对应的记录，用NULL填充。<br>语法：<code>select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo right join stuMarks on stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>思考：select <em> from 表1 left join 表2 on 表1.公共字段=表2.公共字段和select </em> from 表2 right join 表1 on 表1.公共字段=表2.公共字段是否一样？<br>答：一样。</p>
</blockquote>
<h4 id="交叉连接【cross-join】"><a href="#交叉连接【cross-join】" class="headerlink" title="交叉连接【cross join】"></a>交叉连接【cross join】</h4><p>1、如果没有连接表达式返回的是笛卡尔积<br><code>mysql&gt; select * from stuinfo cross join stuMarks;</code><br>等价于<br><code>mysql&gt; select * from stuinfo,stuMarks;</code><br>2、如果有连接表达式等价于内连接</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo cross join stuMarks on stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div></pre></td></tr></table></figure>
<h4 id="自然连接【natural】"><a href="#自然连接【natural】" class="headerlink" title="自然连接【natural】"></a>自然连接【natural】</h4><p>自动的判断连接条件，它是通过同名字段来判断的<br>自然连接又分为：<br>1、    自然内连接        natural join<br><code>select * from stuinfo natural join stuMarks;</code><br>2、    自然左外连接        natural left join<br><code>select * from stuinfo natural left join stuMarks;</code><br>3、    自然右外连接        natural right join<br><code>select * from stuinfo natural rigth join stuMarks;</code></p>
<blockquote>
<p>自然连接结论：<br>1、    表连接通过同名的字段来连接的<br>2、    如果没有同名的字段返回笛卡尔积<br>3、    会对结果进行整理，整理的规则如下<br>a)    连接字段保留一个<br>b)    连接字段放在最前面</p>
</blockquote>
<h4 id="using"><a href="#using" class="headerlink" title="using()"></a>using()</h4><p>1、用来指定连接字段。<br>2、using()也会对连接字段进行整理，整理方式和自然连接是一样的。<br><code>select * from stuinfo inner join stuMarks using(id);</code></p>
<h3 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h3><p>1、    语法：select 语句 where 条件 (select … from 表)<br>2、    外面的查询称为父查询，括号中的查询称为子查询<br>3、    子查询为父查询提供查询条件</p>
<h4 id="子查询-1"><a href="#子查询-1" class="headerlink" title="=子查询"></a>=子查询</h4><p>使用表连接的方法</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select stuName,writtenExam from stuinfo natural join stuMarks having writtenExam&gt;60;</div><div class="line">+--------------+-------------+</div><div class="line">| stuName      | writtenExam |</div><div class="line">+--------------+-------------+</div><div class="line">| 李斯文       |          80 |</div><div class="line">| 欧阳俊雄     |          65 |</div><div class="line">| 张秋丽       |          77 |</div><div class="line">+--------------+-------------+</div><div class="line">3 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>使用子查询方法</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where stuNo in(select stuNo from stuMarks where writtenExam&gt;60);</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">3 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>找出最高分</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where stuNo=(select stuNo from stuMarks order by writtenExam desc limit 1);</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from stuinfo where stuNo=(select stuNo from stuMarks where writtenExam=(select max(writtenExam) from stuMarks));</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="in-not-in子查询"><a href="#in-not-in子查询" class="headerlink" title="in|not in子查询"></a>in|not in子查询</h4><p>用于子查询的返回结果多个值</p>
<h4 id="some-、any-、all"><a href="#some-、any-、all" class="headerlink" title="some()、any()、all()"></a>some()、any()、all()</h4><p>some:一些<br>any:一些        和some()是一样的  类似于in<br>all:全部</p>
<p>=some（=any） 等于其中任何一个<br>!=some（!=any）不等于其中任何一个就可以了<br>=all 等于其中的所有<br>!=all 不等于其中的所有</p>
<blockquote>
<p>思考：!=some()和not in一样吗？<br>答：不一样；!=some()表示父查询的记录只要不等于some中的任意一条即可。<br>!=all()才和not in一样</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where stuNo=some(select stuNo from stuMarks where writtenExam&gt;60);</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from stuinfo where stuNo=any(select stuNo from stuMarks where writtenExam&gt;60);</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from stuinfo where stuNo=all(select stuNo from stuMarks where writtenExam&gt;60);</div><div class="line">Empty set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="exists和not-exists"><a href="#exists和not-exists" class="headerlink" title="exists和not exists"></a>exists和not exists</h4><p>如果有人大于等于80分就显示所有的学生</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where exists (select * from stuMarks where writtenExam&gt;=80);</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       |</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |</div><div class="line">| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       |</div><div class="line">| s25319 | 梅超风       | 女     |     23 |       5 | 河北       |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">7 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>如果没有人超过80分就显示所有的学生</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where not exists (select * from stuMarks where writtenExam&gt;=80);</div><div class="line">Empty set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="子查询分类"><a href="#子查询分类" class="headerlink" title="子查询分类"></a>子查询分类</h4><p>1、    标量子查询：子查询返回的结果就一个<br>2、    列子查询：子查询返回的结果是一个列表<br>3、    行子查询：子查询返回的结果返回一行<br>4、    表子查询：子查询返回的结果当成一个表(一定要取别名)</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select *,count(*) from (select * from stuinfo order by stuAge desc) as new1 group by stuSex;</div><div class="line">+--------+--------------+--------+--------+---------+------------+----------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | count(*) |</div><div class="line">+--------+--------------+--------+--------+---------+------------+----------+</div><div class="line">| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       |        3 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       |        4 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+----------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>子查询一定要取别名</p>
</blockquote>
<h3 id="视图【view】"><a href="#视图【view】" class="headerlink" title="视图【view】"></a>视图【view】</h3><p>1、    视图是一张虚拟表，它表示一张表的部分或多张表的综合的结构。<br>2、    视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。</p>
<h4 id="创建视图"><a href="#创建视图" class="headerlink" title="创建视图"></a>创建视图</h4><p>语法</p>
<p>create [or replace] view 视图的名称<br>as<br>    select语句</p>
<p>因为视图是一个表结构，所以创建视图后，会在数据库文件夹中多一个与视图名同名的.frm文件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create view vw_stu</div><div class="line">    -&gt; as</div><div class="line">    -&gt;   select * from stuinfo nutural join stumarks using(stuno);</div><div class="line">Query OK, 0 rows affected (0.02 sec)</div></pre></td></tr></table></figure>
<h4 id="查询视图"><a href="#查询视图" class="headerlink" title="查询视图"></a>查询视图</h4><p>视图是一张虚拟表，视图的用法和表的用法一样</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from vw_stu;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="查看视图的结构"><a href="#查看视图的结构" class="headerlink" title="查看视图的结构"></a>查看视图的结构</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line">mysql&gt; desc vw_stu;</div><div class="line">+-------------+-------------+------+-----+---------+-------+</div><div class="line">| Field       | Type        | Null | Key | Default | Extra |</div><div class="line">+-------------+-------------+------+-----+---------+-------+</div><div class="line">| stuNo       | char(6)     | NO   |     | NULL    |       |</div><div class="line">| stuName     | varchar(10) | NO   |     | NULL    |       |</div><div class="line">| stuSex      | char(2)     | NO   |     | NULL    |       |</div><div class="line">| stuAge      | tinyint(4)  | NO   |     | NULL    |       |</div><div class="line">| stuSeat     | tinyint(4)  | NO   |     | NULL    |       |</div><div class="line">| stuAddress  | varchar(10) | NO   |     | NULL    |       |</div><div class="line">| examNo      | char(7)     | NO   |     | NULL    |       |</div><div class="line">| writtenExam | int(11)     | YES  |     | NULL    |       |</div><div class="line">| labExam     | int(11)     | YES  |     | NULL    |       |</div><div class="line">+-------------+-------------+------+-----+---------+-------+</div><div class="line">9 rows in set (0.02 sec)</div></pre></td></tr></table></figure>
<h4 id="查看创建视图的语法"><a href="#查看创建视图的语法" class="headerlink" title="查看创建视图的语法"></a>查看创建视图的语法</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show create view vw_stu \g</div><div class="line">+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+</div><div class="line">| View   | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | character_set_client | collation_connection |</div><div class="line">+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+</div><div class="line">| vw_stu | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_stu` AS select `nutural`.`stuNo` AS `stuNo`,`nutural`.`stuName` AS `stuName`,`nutural`.`stuSex` AS `stuSex`,`nutural`.`stuAge` AS `stuAge`,`nutural`.`stuSeat` AS `stuSeat`,`nutural`.`stuAddress` AS `stuAddress`,`stumarks`.`examNo` AS `examNo`,`stumarks`.`writtenExam` AS `writtenExam`,`stumarks`.`labExam` AS `labExam` from (`stuinfo` `nutural` join `stumarks` on((`nutural`.`stuNo` = `stumarks`.`stuNo`))) | utf8                 | utf8_general_ci      |</div><div class="line">+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="显示所有视图"><a href="#显示所有视图" class="headerlink" title="显示所有视图"></a>显示所有视图</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show tables;</div><div class="line">+-----------------+</div><div class="line">| Tables_in_uiste |</div><div class="line">+-----------------+</div><div class="line">| stuMarks        |</div><div class="line">| stuinfo         |</div><div class="line">| vw_stu          |</div><div class="line">+-----------------+</div><div class="line">3 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<h4 id="精确查找视图"><a href="#精确查找视图" class="headerlink" title="精确查找视图"></a>精确查找视图</h4><p>方法一：information_schema中的views表保存的是视图信息</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select table_name from information_schema.views;</div><div class="line">+------------+</div><div class="line">| table_name |</div><div class="line">+------------+</div><div class="line">| vw_stu     |</div><div class="line">+------------+</div><div class="line">1 row in set (0.12 sec)</div></pre></td></tr></table></figure>
<p>方法二：show table status查看表的属性</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div><div class="line">53</div><div class="line">54</div><div class="line">55</div><div class="line">56</div><div class="line">57</div><div class="line">58</div><div class="line">59</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show table status\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">           Name: stuMarks</div><div class="line">         Engine: InnoDB</div><div class="line">        Version: 10</div><div class="line">     Row_format: Compact</div><div class="line">           Rows: 5</div><div class="line"> Avg_row_length: 3276</div><div class="line">    Data_length: 16384</div><div class="line">Max_data_length: 0</div><div class="line">   Index_length: 0</div><div class="line">      Data_free: 0</div><div class="line"> Auto_increment: NULL</div><div class="line">    Create_time: 2016-08-31 17:18:54</div><div class="line">    Update_time: NULL</div><div class="line">     Check_time: NULL</div><div class="line">      Collation: utf8_general_ci</div><div class="line">       Checksum: NULL</div><div class="line"> Create_options: </div><div class="line">        Comment: </div><div class="line">*************************** 2. row ***************************</div><div class="line">           Name: stuinfo</div><div class="line">         Engine: InnoDB</div><div class="line">        Version: 10</div><div class="line">     Row_format: Compact</div><div class="line">           Rows: 7</div><div class="line"> Avg_row_length: 2340</div><div class="line">    Data_length: 16384</div><div class="line">Max_data_length: 0</div><div class="line">   Index_length: 0</div><div class="line">      Data_free: 0</div><div class="line"> Auto_increment: NULL</div><div class="line">    Create_time: 2016-08-31 17:18:54</div><div class="line">    Update_time: NULL</div><div class="line">     Check_time: NULL</div><div class="line">      Collation: utf8_general_ci</div><div class="line">       Checksum: NULL</div><div class="line"> Create_options: </div><div class="line">        Comment: </div><div class="line">*************************** 3. row ***************************</div><div class="line">           Name: vw_stu</div><div class="line">         Engine: NULL</div><div class="line">        Version: NULL</div><div class="line">     Row_format: NULL</div><div class="line">           Rows: NULL</div><div class="line"> Avg_row_length: NULL</div><div class="line">    Data_length: NULL</div><div class="line">Max_data_length: NULL</div><div class="line">   Index_length: NULL</div><div class="line">      Data_free: NULL</div><div class="line"> Auto_increment: NULL</div><div class="line">    Create_time: NULL</div><div class="line">    Update_time: NULL</div><div class="line">     Check_time: NULL</div><div class="line">      Collation: NULL</div><div class="line">       Checksum: NULL</div><div class="line"> Create_options: NULL</div><div class="line">        Comment: VIEW</div><div class="line">3 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>显示所有视图的语法是</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show table status where comment=&apos;view&apos;\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">           Name: vw_stu</div><div class="line">         Engine: NULL</div><div class="line">        Version: NULL</div><div class="line">     Row_format: NULL</div><div class="line">           Rows: NULL</div><div class="line"> Avg_row_length: NULL</div><div class="line">    Data_length: NULL</div><div class="line">Max_data_length: NULL</div><div class="line">   Index_length: NULL</div><div class="line">      Data_free: NULL</div><div class="line"> Auto_increment: NULL</div><div class="line">    Create_time: NULL</div><div class="line">    Update_time: NULL</div><div class="line">     Check_time: NULL</div><div class="line">      Collation: NULL</div><div class="line">       Checksum: NULL</div><div class="line"> Create_options: NULL</div><div class="line">        Comment: VIEW</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="更改视图"><a href="#更改视图" class="headerlink" title="更改视图"></a>更改视图</h4><p>语法：<br>alter view 视图名<br>as<br>    select 语句</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; alter view vw_stu</div><div class="line">    -&gt; as</div><div class="line">    -&gt;  select stuaddress,count(stuaddress) from stuinfo left join stumarks using(stuno) group by stusex;         Query OK, 0 rows affected (0.02 sec)</div><div class="line">mysql&gt; select * from vw_stu;</div><div class="line">+------------+-------------------+</div><div class="line">| stuaddress | count(stuaddress) |</div><div class="line">+------------+-------------------+</div><div class="line">| 北京       |                 3 |</div><div class="line">| 上海       |                 4 |</div><div class="line">+------------+-------------------+</div><div class="line">2 rows in set (0.02 sec)</div></pre></td></tr></table></figure>
<h4 id="删除视图"><a href="#删除视图" class="headerlink" title="删除视图"></a>删除视图</h4><p>语法：<code>drop view [if exists] 视图1,视图2,…</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line">mysql&gt; drop view if exists vw_stu;</div><div class="line">Query OK, 0 rows affected (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; select table_name from information_schema.views;</div><div class="line">Empty set (0.03 sec)</div></pre></td></tr></table></figure>
<h4 id="视图的作用"><a href="#视图的作用" class="headerlink" title="视图的作用"></a>视图的作用</h4><p>1、    筛选数据，防止未经许可访问敏感数据<br>2、    增加表的兼容性<br>3、    隐藏表结构<br>4、    降低SQL语句的复杂度</p>
<h4 id="视图的算法"><a href="#视图的算法" class="headerlink" title="视图的算法"></a>视图的算法</h4><p>找出语文成绩最高的男生和女生<br>既然子查询当成一个表，我们可以用视图来代替<br>将子查询写法改为如下</p>
<blockquote>
<p>结果不正确！原因：这是因为视图的算法造成的</p>
</blockquote>
<h4 id="视图的算法-1"><a href="#视图的算法-1" class="headerlink" title="视图的算法"></a>视图的算法</h4><p>1、    merge：合并算法，将视图的语句和外层的语句合并后在执行。<br>2、    temptable：临时表算法，将视图生成一个临时表，再执行外层语句<br>3、    undefined：未定义，MySQL到底用merge还是用temptable由MySQL决<br>定，这是一个默认的算法，一般视图都会选择merge算法，因为merge效率高。</p>
<h4 id="解决"><a href="#解决" class="headerlink" title="解决"></a>解决</h4><p>在创建视图的时候指定视图的算法<br>create view algorithm=temptable 视图名<br>as<br>    select 语句    </p>
<p>重写视图</p>
<p>重新执行查询</p>
<h3 id="事务安全【transaction】"><a href="#事务安全【transaction】" class="headerlink" title="事务安全【transaction】"></a>事务安全【transaction】</h3><p>1、    事务是一个不可分割的执行单元<br>2、    事务作为一个整体要么一起执行，要么一起回滚</p>
<h4 id="开启事务1"><a href="#开启事务1" class="headerlink" title="开启事务1"></a>开启事务1</h4><p>语法：<code>start transaction或begin [work]</code></p>
<h4 id="执行sql更新语句2"><a href="#执行sql更新语句2" class="headerlink" title="执行sql更新语句2"></a>执行sql更新语句2</h4><p><code>update 表名 set 字段名=字段内容 条件</code></p>
<h4 id="设置还原点3"><a href="#设置还原点3" class="headerlink" title="设置还原点3"></a>设置还原点3</h4><p><code>savepoint sp1</code></p>
<h4 id="执行sql更新语句4"><a href="#执行sql更新语句4" class="headerlink" title="执行sql更新语句4"></a>执行sql更新语句4</h4><h4 id="成功提交事务5"><a href="#成功提交事务5" class="headerlink" title="成功提交事务5"></a>成功提交事务5</h4><p>语法：commit</p>
<h4 id="失败回滚事务5"><a href="#失败回滚事务5" class="headerlink" title="失败回滚事务5"></a>失败回滚事务5</h4><p>语法：rollback</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div></pre></td><td class="code"><pre><div class="line">mysql&gt; start transaction;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into t1 values (4,4);</div><div class="line">Query OK, 1 row affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from t1;</div><div class="line">+------+------+</div><div class="line">| id   | num1 |</div><div class="line">+------+------+</div><div class="line">|    1 |    1 |</div><div class="line">|    2 |    2 |</div><div class="line">|    3 |    3 |</div><div class="line">|    4 |    4 |</div><div class="line">+------+------+</div><div class="line">4 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; rollback;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from t1;</div><div class="line">+------+------+</div><div class="line">| id   | num1 |</div><div class="line">+------+------+</div><div class="line">|    1 |    1 |</div><div class="line">|    2 |    2 |</div><div class="line">|    3 |    3 |</div><div class="line">+------+------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; begin;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into t1 values (4,4);</div><div class="line">Query OK, 1 row affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; commit;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from t1;</div><div class="line">+------+------+</div><div class="line">| id   | num1 |</div><div class="line">+------+------+</div><div class="line">|    1 |    1 |</div><div class="line">|    2 |    2 |</div><div class="line">|    3 |    3 |</div><div class="line">|    4 |    4 |</div><div class="line">+------+------+</div><div class="line">4 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>思考：事务什么时候产生？什么时候结束？<br>答：开启的时候产生，提交事务或回滚事务都结束<br>脚下留心：只有innodb和BDB才支持事务，myisam不支持事务。</p>
</blockquote>
<h4 id="事务的特性（ACID）"><a href="#事务的特性（ACID）" class="headerlink" title="事务的特性（ACID）"></a>事务的特性（ACID）</h4><p>1、    原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行。<br>2、    一致性（Consistency）：事务完成时，数据必须处于一致的状态。<br>3、    隔离性（Isolation）：每个事务都是相互隔离的<br>4、    永久性（Durability）：事务完成后，对数据的修改是永久性的。</p>
<h4 id="自动提交事务"><a href="#自动提交事务" class="headerlink" title="自动提交事务"></a>自动提交事务</h4><p>默认情况下，每个独立的SQL语句都在自动提交事务的。<br>通过如下语句查看是否自动提交事务</p>
<p>可以更改自动提交事务<br>set autocommit=0 | 1；</p>
<h4 id="MySQL的锁的机制"><a href="#MySQL的锁的机制" class="headerlink" title="MySQL的锁的机制"></a>MySQL的锁的机制</h4><p>当在对数据表进行并发(同一时刻会有多个客户端操作同一条记录)操作时，就会触发MySQL的锁的机制<br>①、表锁当某个客户端对一个表进行更改操作时会锁住整个表(其他客户端只能等待)非键值<br>②、行锁–键值</p>
<h3 id="索引【index】"><a href="#索引【index】" class="headerlink" title="索引【index】"></a>索引【index】</h3><p>索引的优点：查询速度快<br>索引的缺点：<br>1、    增、删、改（数据操作语句）效率低了<br>2、    索引占用空间</p>
<h4 id="索引的类型"><a href="#索引的类型" class="headerlink" title="索引的类型"></a>索引的类型</h4><p>1、    普通索引<br>2、    唯一索引（唯一键）<br>3、    主键索引：只要主键就自动创建主键索引，不需要手动创建。<br>4、    全文索引，搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。</p>
<h4 id="创建普通索引【create-index】"><a href="#创建普通索引【create-index】" class="headerlink" title="创建普通索引【create index】"></a>创建普通索引【create index】</h4><p>语法：<code>create index 索引名 on 表名 （字段名）</code></p>
<p>创建索引<br>查看索引</p>
<h4 id="通过修改表的方式创建索引"><a href="#通过修改表的方式创建索引" class="headerlink" title="通过修改表的方式创建索引"></a>通过修改表的方式创建索引</h4><p>语法：<code>alter table 表名 add index [索引的名称] （列名）</code><br>创建索引</p>
<h4 id="创建表的时候指定索引"><a href="#创建表的时候指定索引" class="headerlink" title="创建表的时候指定索引"></a>创建表的时候指定索引</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create table temp(</div><div class="line">    -&gt; id int,</div><div class="line">    -&gt; name varchar(20),</div><div class="line">    -&gt; age tinyint,</div><div class="line">    -&gt; index ix_temp_name(name),		--创建索引</div><div class="line">    -&gt; key(age)				--创建索引</div><div class="line">    -&gt; );</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; show create table temp\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">       Table: temp</div><div class="line">Create Table: CREATE TABLE `temp` (</div><div class="line">  `id` int(11) DEFAULT NULL,</div><div class="line">  `name` varchar(20) DEFAULT NULL,</div><div class="line">  `age` tinyint(4) DEFAULT NULL,</div><div class="line">  KEY `ix_temp_name` (`name`),</div><div class="line">  KEY `age` (`age`)</div><div class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="创建唯一索引"><a href="#创建唯一索引" class="headerlink" title="创建唯一索引"></a>创建唯一索引</h4><p>语法一：create unique index 索引名 on 表名 （字段名）<br>语法二：alter table 表名 add unqiue index [索引的名称] （列名）<br>语法三：创建表的时候添加唯一索引，和创建唯一键是一样的。</p>
<h4 id="创建多列组合索引"><a href="#创建多列组合索引" class="headerlink" title="创建多列组合索引"></a>创建多列组合索引</h4><h4 id="删除索引"><a href="#删除索引" class="headerlink" title="删除索引"></a>删除索引</h4><p>语法：<code>drop index 索引名 on 表名</code></p>
<h4 id="创建索引的指导原则"><a href="#创建索引的指导原则" class="headerlink" title="创建索引的指导原则"></a>创建索引的指导原则</h4><p>1、    该列用于频繁搜索<br>2、    改列用于排序<br>3、    公共字段要创建索引<br>4、    如果表中的数据很少，不需要创建索引。MySQL搜索索引的时间比逐条搜索数据的时间要长。<br>5、    如果一个字段上的数据只有几个不同的值，改字段不适合做索引，比如性别。</p>
<h3 id="存储过程【procedure】"><a href="#存储过程【procedure】" class="headerlink" title="存储过程【procedure】"></a>存储过程【procedure】</h3><h4 id="存储过程的优点"><a href="#存储过程的优点" class="headerlink" title="存储过程的优点"></a>存储过程的优点</h4><p>1、    存储过程可以减少网络流量<br>2、    允许模块化设计<br>3、    支持事务</p>
<h4 id="创建存储过程"><a href="#创建存储过程" class="headerlink" title="创建存储过程"></a>创建存储过程</h4><p>语法：<br>create procedure 存储过程名(参数)<br>begin<br>    //sql语句<br>end;</p>
<blockquote>
<p>脚下留心：由于过程中有很多SQL语句，每个语句的结束都要用（；）表示。默认情况下，分号既表示语句结束，又表示向服务器发送SQL语句。我们希望分号仅表示语句的结束，不要将SQL语句发送到服务器执行，通过delimiter来更改结束符。</p>
</blockquote>
<p><code>mysql&gt;delimiter //</code></p>
<h4 id="创建简单的存储过程"><a href="#创建简单的存储过程" class="headerlink" title="创建简单的存储过程"></a>创建简单的存储过程</h4><h4 id="调用存储过程"><a href="#调用存储过程" class="headerlink" title="调用存储过程"></a>调用存储过程</h4><p>语法：<code>call 存储过程名()</code></p>
<h4 id="删除存储过程"><a href="#删除存储过程" class="headerlink" title="删除存储过程"></a>删除存储过程</h4><p>语法：<code>drop procedure [if exists] 存储过程名</code></p>
<h4 id="创建复杂的存储过程"><a href="#创建复杂的存储过程" class="headerlink" title="创建复杂的存储过程"></a>创建复杂的存储过程</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create procedure proc(param char(6))</div><div class="line">    -&gt; begin</div><div class="line">    -&gt;      select * from stuinfo where stuno=param;</div><div class="line">    -&gt;      select * from stumarks where stuno=param;</div><div class="line">    -&gt; end //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; call proc(&apos;s25301&apos;) //</div><div class="line">+--------+---------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+---------+--------+--------+---------+------------+</div><div class="line">| s25301 | 张秋丽        | 男       |     18 |       1 | 北京</div><div class="line">+--------+---------+--------+--------+---------+------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">+---------+--------+-------------+---------+</div><div class="line">| examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+---------+--------+-------------+---------+</div><div class="line">| s271816 | s25301 |          77 |      82 |</div><div class="line">+---------+--------+-------------+---------+</div><div class="line">1 row in set (0.01 sec)</div></pre></td></tr></table></figure>
<h4 id="存储过程的参数"><a href="#存储过程的参数" class="headerlink" title="存储过程的参数"></a>存储过程的参数</h4><p>存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。<br>存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout）</p>
<h3 id="变量"><a href="#变量" class="headerlink" title="变量"></a>变量</h3><h4 id="局部变量"><a href="#局部变量" class="headerlink" title="局部变量"></a>局部变量</h4><p>1、    通过declare关键字来声明变量<br>2、    语法：declare 变量名 数据类型 [default 默认值]<br>3、    使用set或select …into…给变量赋值</p>
<p>使用select…into…给变量赋值<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create procedure proc(in id char(6))</div><div class="line">    -&gt; begin</div><div class="line">    -&gt;      declare name varchar(20);</div><div class="line">    -&gt;      declare sex char(1);</div><div class="line">    -&gt;      select stuname,stusex into name,sex from stuinfo where stuno=id;</div><div class="line">    -&gt;      select name,sex from dual;</div><div class="line">    -&gt; end //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; call proc(&apos;s25301&apos;)//</div><div class="line">+--------+------+</div><div class="line">| name   | sex  |</div><div class="line">+--------+------+</div><div class="line">| 张秋丽       | 男     |</div><div class="line">+--------+------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure></p>
<p>例题：查找同桌<br>例题：使用set赋值</p>
<h4 id="全局变量"><a href="#全局变量" class="headerlink" title="全局变量"></a>全局变量</h4><p>在变量前面加一个@，就是全局变量<br>全局变量是一个弱类型的变量，它的类型取决于付给变量的值</p>
<h4 id="系统变量"><a href="#系统变量" class="headerlink" title="系统变量"></a>系统变量</h4><p>只要变量名前面有两个@@的肯定是系统变量</p>
<h3 id="带输出参数的存储过程【out】"><a href="#带输出参数的存储过程【out】" class="headerlink" title="带输出参数的存储过程【out】"></a>带输出参数的存储过程【out】</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create procedure proc(num int,out result int)</div><div class="line">    -&gt; begin</div><div class="line">    -&gt;      set result=num*num;</div><div class="line">    -&gt; end //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; call proc(5,@result) //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select @result from dual //</div><div class="line">+---------+</div><div class="line">| @result |</div><div class="line">+---------+</div><div class="line">|      25 |</div><div class="line">+---------+</div></pre></td></tr></table></figure>
<h3 id="带输入输出参数的存储过程【inout】"><a href="#带输入输出参数的存储过程【inout】" class="headerlink" title="带输入输出参数的存储过程【inout】"></a>带输入输出参数的存储过程【inout】</h3><h3 id="查看存储过程的信息"><a href="#查看存储过程的信息" class="headerlink" title="查看存储过程的信息"></a>查看存储过程的信息</h3><p>—————内连接—————-<br>–语法一：<br><code>select i.stuno,stuname,writtenexam from stuinfo i inner join stumarks s on i.stuno=s.stuno;</code><br>–语法二：<br><code>select stuinfo.stuno,stuname,writtenexam from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;</code><br>–inner 可以省略<br><code>select i.stuno,stuname,writtenexam from stuinfo i join stumarks s on i.stuno=s.stuno;</code><br>————–左外连接,右外连接———-<br><code>select stuinfo.stuno,stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;</code></p>
<p><code>select stuinfo.stuno,stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;</code></p>
<p>————–自然连接—————<br>–自然内连接<br><code>select * from t1 natural join t2;</code><br>–自然左外连接<br><code>select * from t1 natural left join t2;</code><br>–自然右外连接<br><code>select * from t1 natural right join t2;</code></p>
<p>—————-using()——————-<br><code>select * from t1 inner join t2 using(id);</code><br><code>select * from t1 left join t2 using(id);</code></p>
<p>——————-子查询————————–<br>–查找80分的学生<br>–方法一<br><code>select stuname,writtenexam from stuinfo natural join stumarks where writtenexam=80;</code><br>–方法二<br><code>select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);</code></p>
<p>–找出最高分<br><code>select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=(select max(writtenexam) from stumarks));</code></p>
<p>–查找及格的学生<br><code>select * from stuinfo where stuno in (select stuno from stumarks where writtenexam&gt;=60);</code></p>
<p>–查找不及格的学生<br><code>select * from stuinfo where stuno not in (select stuno from stumarks where writtenexam&gt;=60);</code></p>
<p>–查找缺考的学生<br><code>select * from stuinfo where stuno in (select stuno)</code></p>
<p><code>select * from stuinfo where stuno in (select stuno from stuinfo natural left join stumarks where writtenexam is null);</code></p>
<p>–some</p>
<p><code>select * from stuinfo where stuno=some(select stuno from stumarks where writtenexam&gt;=60);</code></p>
<p>–exits<br><code>select * from stuinfo where exists (select * from stumarks where writtenexam&gt;=80)</code></p>
<p>–行子查询<br><code>select * from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);</code></p>
<p>–表子查询<br><code>select * from (select * from stu order by ch desc) as t group by stusex;</code></p>
<p>–示地区和每个地区参见考试的人数，并按人数降序排列<br><code>select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc;</code></p>
<p>–显示有学生参见考试的地区<br><code>select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress having c&gt;0;</code></p>
<p><code>select distinct stuaddress from stuinfo natural right join stumarks where stuaddress is not null;</code></p>
<p>–显示男生和女生的人数<br><code>select stusex,count(*) from stuinfo where stusex=&#39;男&#39; union select stusex,count(*) from stuinfo where stusex=&#39;女&#39;;</code></p>
<p><code>select sum(stusex=&#39;男&#39;) 男,sum(stusex=&#39;女&#39;) 女 from stuinfo;</code></p>
<p>–显示每个地区的男生和女生，以及总人数<br><code>select stuaddress,count(*) 总人数,sum(stusex=&#39;男&#39;) 男,sum(stusex=&#39;女&#39;) 女 from stuinfo group by stuaddress;</code></p>
<p>———————–视图———————–</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">create view vw_stu</div><div class="line">as</div><div class="line">  select stuname,stusex,writtenexam from stuinfo inner join stumarks using (stuno);</div></pre></td></tr></table></figure>
<p><code>select * from (select * from stu order by ch desc) t group by stusex;</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">create or replace algorithm=temptable view vw1</div><div class="line">as</div><div class="line">select * from stu order by ch desc;</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">--</div><div class="line">create table temp(</div><div class="line">id int,</div><div class="line">name varchar(20),</div><div class="line">age tinyint,</div><div class="line">index ix_temp_name(name),</div><div class="line">key(age)</div><div class="line">)</div></pre></td></tr></table></figure>
<h3 id="多表查询分类-1"><a href="#多表查询分类-1" class="headerlink" title="多表查询分类"></a>多表查询分类</h3><p>将多个表的数据横向的联合起来。<br>1、    内连接<br>2、    外连接<br>a)    左外连接<br>b)    右外连接<br>3、    交叉连接<br>4、    自然连接</p>

      
    </div>

    <div>
      
        

      
    </div>

    <div>
      
        

      
    </div>

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/MySQL巩固与加强/" rel="tag">#MySQL巩固与加强</a>
          
        </div>
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/20160901-1.html" rel="next" title="Day9-MySQL 数据库设计及步骤">
                <i class="fa fa-chevron-left"></i> Day9-MySQL 数据库设计及步骤
              </a>
            
          </div>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/20160902-2.html" rel="prev" title="Day10(+)-MySQL 多表查询、备份、账户权限">
                Day10(+)-MySQL 多表查询、备份、账户权限 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </article>



    <div class="post-spread">
      
        <!-- Go to www.addthis.com/dashboard to customize your tools -->
<script type = "text/javascript" src = "//s7.addthis.com/js/300/addthis_widget.js#pubid=ra-57838eee5e2620d0" async = "async" ></script>
      
    </div>
  </div>


          </div>
          

  <p>热评文章</p>
  <div class="ds-top-threads" data-range="weekly" data-num-items="4"></div>


          
  <div class="comments" id="comments">
    
      <div class="ds-thread" data-thread-key="20160902-1.html"
           data-title="Day10-MySQL 多表查询、视图、事物、索引" data-url="http://blog.uiste.com/20160902-1.html">
      </div>
    
  </div>


        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap" >
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview sidebar-panel ">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="/images/avatar.png"
               alt="uiste" />
          <p class="site-author-name" itemprop="name">uiste</p>
          <p class="site-description motion-element" itemprop="description">Stay Hungry，Stay Foolish</p>
        </div>
        <nav class="site-state motion-element">
          <div class="site-state-item site-state-posts">
            <a href="/archives">
              <span class="site-state-item-count">70</span>
              <span class="site-state-item-name">日志</span>
            </a>
          </div>

          
            <div class="site-state-item site-state-categories">
              
                <span class="site-state-item-count">13</span>
                <span class="site-state-item-name">分类</span>
              
            </div>
          

          
            <div class="site-state-item site-state-tags">
              <a href="/tags">
                <span class="site-state-item-count">55</span>
                <span class="site-state-item-name">标签</span>
              </a>
            </div>
          

        </nav>

        
          <div class="feed-link motion-element">
            <a href="/atom.xml" rel="alternate">
              <i class="fa fa-rss"></i>
              RSS
            </a>
          </div>
        

        <div class="links-of-author motion-element">
          
            
              <span class="links-of-author-item">
                <a href="https://github.com/uiste" target="_blank" rel="external nofollow" title="GitHub">
                  
                    <i class="fa fa-fw fa-github"></i>
                  
                  GitHub
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://weibo.com/uiste" target="_blank" rel="external nofollow" title="微博">
                  
                    <i class="fa fa-fw fa-globe"></i>
                  
                  微博
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://www.zhihu.com/people/uiste" target="_blank" rel="external nofollow" title="知乎">
                  
                    <i class="fa fa-fw fa-globe"></i>
                  
                  知乎
                </a>
              </span>
            
          
        </div>

        
        

        
        
          <div class="links-of-blogroll motion-element links-of-blogroll-inline">
            <div class="links-of-blogroll-title">
              <i class="fa  fa-fw fa-globe"></i>
              友情链接
            </div>
            <ul class="links-of-blogroll-list">
              
                <li class="links-of-blogroll-item">
                  <a href="http://www.uiste.com" title="uiste 个人博客" target="_blank">uiste 个人博客</a>
                </li>
              
                <li class="links-of-blogroll-item">
                  <a href="http://uiste.github.io" title="uiste 技术博客" target="_blank">uiste 技术博客</a>
                </li>
              
            </ul>
          </div>
        

      </section>

      
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">
            
              
            
            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#多表查询分类"><span class="nav-number">1.</span> <span class="nav-text">多表查询分类</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#union联合查询"><span class="nav-number">1.1.</span> <span class="nav-text">union联合查询</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#内连接【inner-join】"><span class="nav-number">1.2.</span> <span class="nav-text">内连接【inner join】</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#左外连接【left-join】"><span class="nav-number">1.3.</span> <span class="nav-text">左外连接【left join】</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#右外连接【right-join】"><span class="nav-number">1.4.</span> <span class="nav-text">右外连接【right join】</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#交叉连接【cross-join】"><span class="nav-number">1.5.</span> <span class="nav-text">交叉连接【cross join】</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#自然连接【natural】"><span class="nav-number">1.6.</span> <span class="nav-text">自然连接【natural】</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#using"><span class="nav-number">1.7.</span> <span class="nav-text">using()</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#子查询"><span class="nav-number">2.</span> <span class="nav-text">子查询</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#子查询-1"><span class="nav-number">2.1.</span> <span class="nav-text">=子查询</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#in-not-in子查询"><span class="nav-number">2.2.</span> <span class="nav-text">in|not in子查询</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#some-、any-、all"><span class="nav-number">2.3.</span> <span class="nav-text">some()、any()、all()</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#exists和not-exists"><span class="nav-number">2.4.</span> <span class="nav-text">exists和not exists</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#子查询分类"><span class="nav-number">2.5.</span> <span class="nav-text">子查询分类</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#视图【view】"><span class="nav-number">3.</span> <span class="nav-text">视图【view】</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#创建视图"><span class="nav-number">3.1.</span> <span class="nav-text">创建视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#查询视图"><span class="nav-number">3.2.</span> <span class="nav-text">查询视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#查看视图的结构"><span class="nav-number">3.3.</span> <span class="nav-text">查看视图的结构</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#查看创建视图的语法"><span class="nav-number">3.4.</span> <span class="nav-text">查看创建视图的语法</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#显示所有视图"><span class="nav-number">3.5.</span> <span class="nav-text">显示所有视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#精确查找视图"><span class="nav-number">3.6.</span> <span class="nav-text">精确查找视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#更改视图"><span class="nav-number">3.7.</span> <span class="nav-text">更改视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#删除视图"><span class="nav-number">3.8.</span> <span class="nav-text">删除视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#视图的作用"><span class="nav-number">3.9.</span> <span class="nav-text">视图的作用</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#视图的算法"><span class="nav-number">3.10.</span> <span class="nav-text">视图的算法</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#视图的算法-1"><span class="nav-number">3.11.</span> <span class="nav-text">视图的算法</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#解决"><span class="nav-number">3.12.</span> <span class="nav-text">解决</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#事务安全【transaction】"><span class="nav-number">4.</span> <span class="nav-text">事务安全【transaction】</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#开启事务1"><span class="nav-number">4.1.</span> <span class="nav-text">开启事务1</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#执行sql更新语句2"><span class="nav-number">4.2.</span> <span class="nav-text">执行sql更新语句2</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#设置还原点3"><span class="nav-number">4.3.</span> <span class="nav-text">设置还原点3</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#执行sql更新语句4"><span class="nav-number">4.4.</span> <span class="nav-text">执行sql更新语句4</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#成功提交事务5"><span class="nav-number">4.5.</span> <span class="nav-text">成功提交事务5</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#失败回滚事务5"><span class="nav-number">4.6.</span> <span class="nav-text">失败回滚事务5</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#事务的特性（ACID）"><span class="nav-number">4.7.</span> <span class="nav-text">事务的特性（ACID）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#自动提交事务"><span class="nav-number">4.8.</span> <span class="nav-text">自动提交事务</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#MySQL的锁的机制"><span class="nav-number">4.9.</span> <span class="nav-text">MySQL的锁的机制</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#索引【index】"><span class="nav-number">5.</span> <span class="nav-text">索引【index】</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#索引的类型"><span class="nav-number">5.1.</span> <span class="nav-text">索引的类型</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#创建普通索引【create-index】"><span class="nav-number">5.2.</span> <span class="nav-text">创建普通索引【create index】</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#通过修改表的方式创建索引"><span class="nav-number">5.3.</span> <span class="nav-text">通过修改表的方式创建索引</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#创建表的时候指定索引"><span class="nav-number">5.4.</span> <span class="nav-text">创建表的时候指定索引</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#创建唯一索引"><span class="nav-number">5.5.</span> <span class="nav-text">创建唯一索引</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#创建多列组合索引"><span class="nav-number">5.6.</span> <span class="nav-text">创建多列组合索引</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#删除索引"><span class="nav-number">5.7.</span> <span class="nav-text">删除索引</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#创建索引的指导原则"><span class="nav-number">5.8.</span> <span class="nav-text">创建索引的指导原则</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#存储过程【procedure】"><span class="nav-number">6.</span> <span class="nav-text">存储过程【procedure】</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#存储过程的优点"><span class="nav-number">6.1.</span> <span class="nav-text">存储过程的优点</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#创建存储过程"><span class="nav-number">6.2.</span> <span class="nav-text">创建存储过程</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#创建简单的存储过程"><span class="nav-number">6.3.</span> <span class="nav-text">创建简单的存储过程</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#调用存储过程"><span class="nav-number">6.4.</span> <span class="nav-text">调用存储过程</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#删除存储过程"><span class="nav-number">6.5.</span> <span class="nav-text">删除存储过程</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#创建复杂的存储过程"><span class="nav-number">6.6.</span> <span class="nav-text">创建复杂的存储过程</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#存储过程的参数"><span class="nav-number">6.7.</span> <span class="nav-text">存储过程的参数</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#变量"><span class="nav-number">7.</span> <span class="nav-text">变量</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#局部变量"><span class="nav-number">7.1.</span> <span class="nav-text">局部变量</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#全局变量"><span class="nav-number">7.2.</span> <span class="nav-text">全局变量</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#系统变量"><span class="nav-number">7.3.</span> <span class="nav-text">系统变量</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#带输出参数的存储过程【out】"><span class="nav-number">8.</span> <span class="nav-text">带输出参数的存储过程【out】</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#带输入输出参数的存储过程【inout】"><span class="nav-number">9.</span> <span class="nav-text">带输入输出参数的存储过程【inout】</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查看存储过程的信息"><span class="nav-number">10.</span> <span class="nav-text">查看存储过程的信息</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#多表查询分类-1"><span class="nav-number">11.</span> <span class="nav-text">多表查询分类</span></a></li></ol></div>
            
          </div>
        </section>
      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  &copy;  2015 - 
  <span itemprop="copyrightYear">2016</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">uiste</span>
</div>

<div class="powered-by">
  由 <a class="theme-link" href="http://hexo.io" rel="external nofollow">Hexo</a> 趋势动力
</div>
<!--站长统计-->
<script src="https://s95.cnzz.com/z_stat.php?id=1259894121&web_id=1259894121" language="JavaScript"></script>
<!--<div class="theme-info">
  Theme -
  <a class="theme-link" href="https://github.com/iissnan/hexo-theme-next" rel="external nofollow">
    NexT.Mist
  </a>
</div> -->
&nbsp;&nbsp;&nbsp;本站总点击 <span id="busuanzi_value_site_pv"></span> 次
&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;您是第 <span id="busuanzi_value_site_uv"></span> 位访客

<script async src="https://dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js">
</script>

<script>
(function(){
    var bp = document.createElement('script');
    bp.src = '//push.zhanzhang.baidu.com/push.js';
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(bp, s);
})();
</script>


        

        
      </div>
    </footer>

    <div class="back-to-top">
      <i class="fa fa-arrow-up"></i>
    </div>
  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  



  
  <script type="text/javascript" src="/vendors/jquery/index.js?v=2.1.3"></script>

  
  <script type="text/javascript" src="/vendors/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  
  <script type="text/javascript" src="/vendors/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  
  <script type="text/javascript" src="/vendors/velocity/velocity.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/vendors/velocity/velocity.ui.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/vendors/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.0.1"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.0.1"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.0.1"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.0.1"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.0.1"></script>



  

  
    
  

  <script type="text/javascript">
    var duoshuoQuery = {short_name:"uiste"};
    (function() {
      var ds = document.createElement('script');
      ds.type = 'text/javascript';ds.async = true;
      ds.id = 'duoshuo-script';
      ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
      ds.charset = 'UTF-8';
      (document.getElementsByTagName('head')[0]
      || document.getElementsByTagName('body')[0]).appendChild(ds);
    })();
  </script>

  
    
    <script src="/vendors/ua-parser-js/dist/ua-parser.min.js?v=0.7.9"></script>
    <script src="/js/src/hook-duoshuo.js"></script>
  






  
  
  <script type="text/javascript">
    // Popup Window;
    var isfetched = false;
    // Search DB path;
    var search_path = "search.xml";
    if (search_path.length == 0) {
       search_path = "search.xml";
    }
    var path = "/" + search_path;
    // monitor main search box;

    function proceedsearch() {
      $("body").append('<div class="popoverlay">').css('overflow', 'hidden');
      $('.popup').toggle();

    }
    // search function;
    var searchFunc = function(path, search_id, content_id) {
    'use strict';
    $.ajax({
        url: path,
        dataType: "xml",
        async: true,
        success: function( xmlResponse ) {
            // get the contents from search data
            isfetched = true;
            $('.popup').detach().appendTo('.header-inner');
            var datas = $( "entry", xmlResponse ).map(function() {
                return {
                    title: $( "title", this ).text(),
                    content: $("content",this).text(),
                    url: $( "url" , this).text()
                };
            }).get();
            var $input = document.getElementById(search_id);
            var $resultContent = document.getElementById(content_id);
            $input.addEventListener('input', function(){
                var matchcounts = 0;
                var str='<ul class=\"search-result-list\">';
                var keywords = this.value.trim().toLowerCase().split(/[\s\-]+/);
                $resultContent.innerHTML = "";
                if (this.value.trim().length > 1) {
                // perform local searching
                datas.forEach(function(data) {
                    var isMatch = true;
                    var content_index = [];
                    var data_title = data.title.trim().toLowerCase();
                    var data_content = data.content.trim().replace(/<[^>]+>/g,"").toLowerCase();
                    var data_url = data.url;
                    var index_title = -1;
                    var index_content = -1;
                    var first_occur = -1;
                    // only match artiles with not empty titles and contents
                    if(data_title != '' && data_content != '') {
                        keywords.forEach(function(keyword, i) {
                            index_title = data_title.indexOf(keyword);
                            index_content = data_content.indexOf(keyword);
                            if( index_title < 0 && index_content < 0 ){
                                isMatch = false;
                            } else {
                                if (index_content < 0) {
                                    index_content = 0;
                                }
                                if (i == 0) {
                                    first_occur = index_content;
                                }
                            }
                        });
                    }
                    // show search results
                    if (isMatch) {
                        matchcounts += 1;
                        str += "<li><a href='"+ data_url +"' class='search-result-title'>"+ data_title +"</a>";
                        var content = data.content.trim().replace(/<[^>]+>/g,"");
                        if (first_occur >= 0) {
                            // cut out 100 characters
                            var start = first_occur - 20;
                            var end = first_occur + 80;
                            if(start < 0){
                                start = 0;
                            }
                            if(start == 0){
                                end = 50;
                            }
                            if(end > content.length){
                                end = content.length;
                            }
                            var match_content = content.substring(start, end);
                            // highlight all keywords
                            keywords.forEach(function(keyword){
                                var regS = new RegExp(keyword, "gi");
                                match_content = match_content.replace(regS, "<b class=\"search-keyword\">"+keyword+"</b>");
                            });

                            str += "<p class=\"search-result\">" + match_content +"...</p>"
                        }
                        str += "</li>";
                    }
                })};
                str += "</ul>";
                if (matchcounts == 0) { str = '<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>' }
                if (keywords == "") { str = '<div id="no-result"><i class="fa fa-search fa-5x" /></div>' }
                $resultContent.innerHTML = str;
            });
            proceedsearch();
        }
    });}

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched == false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };

    });

    $('.popup-btn-close').click(function(e){
      $('.popup').hide();
      $(".popoverlay").remove();
      $('body').css('overflow', '');
    });
    $('.popup').click(function(e){
      e.stopPropagation();
    });
  </script>


  

  

  

</body>
</html>
